﻿using System;
using System.Collections.Generic;
using System.Text;
using MeTalker.Model;
using MeTalker.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace MeTalker.DAL
{
    public class UsersDAL:DALBase
    {
        static UsersMod SetModel(DataRow row)
        {
            UsersMod user = new UsersMod();
            user.AutoID = int.Parse(row["AutoID"].ToString());
            user.IsValid = bool.Parse(row["IsValid"].ToString());
            user.Password = row["Password"].ToString();
            user.UserName = row["UserName"].ToString();
            return user;
        }

        public static DataTable GetUsers() {
            try
            {
                string sql = "Select * from Users";// and LoginFlag=0
                return SqlHelper.ExecuteDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }

        public static UsersMod GetUserByIdToMod(int id)
        {
            DataTable dt = GetUserById(id);
            if (dt == null || dt.Rows.Count <= 0)
            {
                return null;
            }
            foreach (DataRow row in dt.Rows)
            {
                return SetModel(row);
            }
            return null;
        }

        public static DataTable GetUserById(int id) {
            try
            {
                string sql = "Select * from Users where AutoID=" + id;
                return SqlHelper.ExecuteDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable GetUser(string userName)
        {
            try
            {
                string sql = "Select * from Users where UserName='" + userName + "'";// and LoginFlag=0
                return SqlHelper.ExecuteDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }

        public static int Update(UsersMod user)
        {
            try
            {
                string sql = "update Users set UserName='{0}', Password='{1}', IsValid={2} where AutoID={3}";
                //sql = string.Format(sql, user.UserName, user.Password, user.IsValid == true ? 1 : 0, user.AutoID);
                sql = string.Format(sql, user.UserName, user.Password, 1, user.AutoID);
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static int Insert(UsersMod user)
        {
            try
            {
                string sql = "insert into Users(UserName,Password,IsValid) values('{0}','{1}',1)";
                sql = string.Format(sql, user.UserName, user.Password);
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static int Delete(UsersMod user)
        {
            try
            {
               return Delete( user.AutoID);
            }
            catch
            {
                return 0;
            }
        }

        public static int Delete(int autoid)
        {
            try
            {
                string sql = "delete Users where AutoID=" + autoid;
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static List<UsersMod> GetUsersToList()
        {
            DataTable dt = GetUsers();
            if (dt == null || dt.Rows.Count <= 0)
            {
                return null;
            }
            List<UsersMod> users = new List<UsersMod>();
            foreach (DataRow row in dt.Rows)
            {
                users.Add( SetModel(row));
            }
            return users;
        }

        public static UsersMod SignUser(UsersMod user)
        {
            DataTable dt = SignUser(user.UserName, user.Password);
            if (dt == null || dt.Rows.Count <= 0)
            {
                return null;
            }
            foreach (DataRow row in dt.Rows)
            {
                return SetModel(row);
            }
            return null;
        }

        public static DataTable SignUser(string userName, string password)
        {
            try
            {
                string sql = "Select * from Users where Password=@Password and UserName=@UserName and IsValid=1";// and LoginFlag=0
                SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
                paramUserName.Value = userName;
                SqlParameter paramPassword = new SqlParameter("@Password", SqlDbType.VarChar, 100, "Password");
                paramPassword.Value = password;
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter[] { paramUserName, paramPassword });
            }
            catch
            {
                return null;
            }
        }
    }
}